﻿<%@ WebHandler Language="C#" Class="ZSDeliveryDetail" %>

using System;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Linq;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Text;
using System.Web.SessionState;
using System.IO;
using ReportBusiness;

public class ZSDeliveryDetail : IHttpHandler
{
    public bool IsReusable { get { return false; } }
    private uMESReportOracleDAL dal = new uMESReportOracleDAL();
    public void ProcessRequest(HttpContext context)
    {
        //批产明细
        if (context.Request.Form["type"] != null && context.Request.Form["type"] == "GetPiChan")
        {
            Dictionary<string, string> para = new Dictionary<string, string>();
            if (context.Request.Form["page"] != null)
            {
                para["page"] = context.Request.Form["page"].ToString();
            }
            if (context.Request.Form["rows"] != null)
            {
                para["rows"] = context.Request.Form["rows"].ToString();
            }

            int pageCount = 0;
            int rowCount = 0;

            //            string sql = @"
            //SELECT to_char(mfg.factorycompletiondate,'mm') || '月' planmonth,mfg.mfgordername,u.attributevalue,
            //        max(ep.finishdate) finishdate,DECODE(ep.usecompleted,0,'未完工',1,'已完工') isfinished,
            //        NVL(u2.attributevalue,DECODE(ot.ordertypename,'PD01','批产','PD02','新机','PD03','新机','PD05','大修','PD06','检修')) ordertypename
            //FROM enginepartplan ep
            //LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
            //LEFT JOIN Userattribute u ON u.parentid=ep.mfgorderid AND u.userattributename='发动机'
            //LEFT JOIN Ordertype ot ON ot.ordertypeid = mfg.ordertypeid
            //LEFT JOIN Userattribute u2 ON u2.parentid=ep.mfgorderid AND u2.userattributename = '订单类别'
            //LEFT JOIN workflowstep ws ON ws.workflowstepid = ep.workflowstepid
            //WHERE extract(year from mfg.factorycompletiondate) = extract(year from SYSDATE)
            //        AND NVL(u2.attributevalue,DECODE(ot.ordertypename,'PD01','批产','PD02','新机','PD03','新机','PD05','大修','PD06','检修')) = '批产'
            //        AND ep.type = 1 AND ws.workflowstepname = '0120-检验试车' AND ws.sequence < 13
            //GROUP BY mfg.factorycompletiondate,mfg.mfgordername,u.attributevalue,
            //            ep.usecompleted,NVL(u2.attributevalue,DECODE(ot.ordertypename,'PD01','批产','PD02','新机','PD03','新机','PD05','大修','PD06','检修'))
            //ORDER BY mfg.factorycompletiondate,u.attributevalue
            //";
            string sql = @"
SELECT t.planmonth,t.mfgordername,t.attributevalue,t.ordertypename,
       CASE WHEN COUNT(t.containerno) = SUM(t.usecompleted) THEN '已完工' ELSE '未完工' END isfinished,
       CASE WHEN COUNT(t.containerno) = SUM(t.usecompleted) THEN MAX(t.finishdate) ELSE NULL END finishdate
         
FROM (
    SELECT  to_char(mfg.factorycompletiondate,'mm') || '月' planmonth,mfg.mfgordername,u.attributevalue,ep.containerno,
            max(ep.finishdate) finishdate,ep.usecompleted,--DECODE(ep.usecompleted,0,'未完工',1,'已完工') isfinished,
            NVL(u2.attributevalue,DECODE(ot.ordertypename,'PD01','批产','PD02','新机','PD03','新机','PD05','大修','PD06','检修')) ordertypename
    FROM enginepartplan ep
    LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
    LEFT JOIN Userattribute u ON u.parentid=ep.mfgorderid AND u.userattributename='发动机'
    LEFT JOIN Ordertype ot ON ot.ordertypeid = mfg.ordertypeid
    LEFT JOIN Userattribute u2 ON u2.parentid=ep.mfgorderid AND u2.userattributename = '订单类别'
    LEFT JOIN workflowstep ws ON ws.workflowstepid = ep.workflowstepid
    LEFT JOIN productbase pb ON pb.productbaseid = mfg.productbaseid
    LEFT JOIN product p ON p.productid = NVL(pb.revofrcdid,mfg.productid)
    WHERE extract(year from mfg.factorycompletiondate) = extract(year from SYSDATE)
          AND NVL(u2.attributevalue,DECODE(ot.ordertypename,'PD01','批产','PD02','新机','PD03','新机','PD05','大修','PD06','检修')) = '批产'
          AND ep.type = 1 AND ws.workflowstepname = '0120-检验试车' AND ws.sequence < 13
          AND ep.containerno IS NOT NULL
    GROUP BY mfg.factorycompletiondate,mfg.mfgordername,u.attributevalue,ep.containerno,
             ep.usecompleted,NVL(u2.attributevalue,DECODE(ot.ordertypename,'PD01','批产','PD02','新机','PD03','新机','PD05','大修','PD06','检修'))
    ORDER BY mfg.mfgordername,u.attributevalue,ep.containerno
) t
GROUP BY t.planmonth,t.mfgordername,t.attributevalue,t.ordertypename
ORDER BY t.mfgordername,t.attributevalue
";
            uMESPagingDataDTO retR = dal.GetPagingDataIns(sql, Convert.ToInt32(para["page"]), Convert.ToInt32(para["rows"]));
            pageCount = Convert.ToInt32(retR.PageCount);
            rowCount = Convert.ToInt32(retR.RowCount);

            var json = JsonConvert.SerializeObject(retR.DBTable);
            context.Response.ContentType = "text/json";
            json = "{\"data\":" + json.ToString()
                    + ",\"count\":" + rowCount
                    + ",\"code\":" + "0"
                    + ",\"msg\":" + "0"
                    + "}";
            context.Response.Write(json.ToString());
            context.Response.End();
        }

        //新机明细
        if (context.Request.Form["type"] != null && context.Request.Form["type"] == "GetXinJi")
        {
            Dictionary<string, string> para = new Dictionary<string, string>();
            if (context.Request.Form["page"] != null)
            {
                para["page"] = context.Request.Form["page"].ToString();
            }
            if (context.Request.Form["rows"] != null)
            {
                para["rows"] = context.Request.Form["rows"].ToString();
            }

            int pageCount = 0;
            int rowCount = 0;

            string sql = @"
SELECT t.*,m.finishdate,CASE WHEN m.finishdate IS NOT NULL THEN '已完工' ELSE '未完工' END isfinished
FROM
(
  SELECT NVL(u2.attributevalue,DECODE(ot.ordertypename,'PD02','新机','PD03','新机','PD06','检修')) ordertypename,u.attributevalue,
         mfg.mfgordername,to_char(mfg.factorycompletiondate,'mm') || '月' planmonth
  FROM mfgorder mfg
  LEFT JOIN Ordertype ot ON ot.ordertypeid = mfg.ordertypeid
  LEFT JOIN Userattribute u ON u.parentid = mfg.mfgorderid AND u.userattributename = '发动机'
  LEFT JOIN Userattribute u2 ON u2.parentid = mfg.mfgorderid AND u2.userattributename = '订单类别'
  WHERE extract(year from mfg.factorycompletiondate) = extract(year from SYSDATE)
        AND NVL(u2.attributevalue,DECODE(ot.ordertypename,'PD01','批产','PD02','新机','PD03','新机','PD05','大修','PD06','检修')) = '新机'
) t
LEFT JOIN
(
  SELECT to_char(mfg.factorycompletiondate,'mm') || '月' planmonth,mfg.mfgordername,u.attributevalue,
         ml.finishdate,NVL(u2.attributevalue,DECODE(ot.ordertypename,'PD01','批产','PD02','新机','PD03','新机','PD05','大修','PD06','检修')) ordertypename
  FROM mfgorder mfg
  LEFT JOIN Orderstatus od ON od.orderstatusid = mfg.orderstatusid
  LEFT JOIN ( SELECT ml.mfgorderid,MAX(ml.operatetime) finishdate FROM mfgorderoperatelog ml GROUP BY ml.mfgorderid ) ml ON ml.mfgorderid = mfg.mfgorderid
  LEFT JOIN Ordertype ot ON ot.ordertypeid = mfg.ordertypeid
  LEFT JOIN Userattribute u ON u.parentid = mfg.mfgorderid AND u.userattributename = '发动机'
  LEFT JOIN Userattribute u2 ON u2.parentid = mfg.mfgorderid AND u2.userattributename = '订单类别'
  WHERE extract(year from mfg.factorycompletiondate) = extract(year from SYSDATE) AND od.orderstatusname='关闭'
        AND extract(year from ml.finishdate) = extract(year from SYSDATE)
        AND NVL(u2.attributevalue,DECODE(ot.ordertypename,'PD01','批产','PD02','新机','PD03','新机','PD05','大修','PD06','检修')) = '新机'
) m ON t.planmonth = m.planmonth AND t.mfgordername = m.mfgordername
ORDER BY t.planmonth,t.attributevalue
";
            uMESPagingDataDTO retR = dal.GetPagingDataIns(sql, Convert.ToInt32(para["page"]), Convert.ToInt32(para["rows"]));
            pageCount = Convert.ToInt32(retR.PageCount);
            rowCount = Convert.ToInt32(retR.RowCount);

            var json = JsonConvert.SerializeObject(retR.DBTable);
            context.Response.ContentType = "text/json";
            json = "{\"data\":" + json.ToString()
                    + ",\"count\":" + rowCount
                    + ",\"code\":" + "0"
                    + ",\"msg\":" + "0"
                    + "}";
            context.Response.Write(json.ToString());
            context.Response.End();
        }

        //检修明细
        if (context.Request.Form["type"] != null && context.Request.Form["type"] == "GetJianXiu")
        {
            Dictionary<string, string> para = new Dictionary<string, string>();
            if (context.Request.Form["page"] != null)
            {
                para["page"] = context.Request.Form["page"].ToString();
            }
            if (context.Request.Form["rows"] != null)
            {
                para["rows"] = context.Request.Form["rows"].ToString();
            }

            int pageCount = 0;
            int rowCount = 0;

            string sql = @"
SELECT t.*,m.finishdate,CASE WHEN m.finishdate IS NOT NULL THEN '已完工' ELSE '未完工' END isfinished
FROM
(
  SELECT NVL(u2.attributevalue,DECODE(ot.ordertypename,'PD02','新机','PD03','新机','PD06','检修')) ordertypename,u.attributevalue,
         mfg.mfgordername,to_char(mfg.factorycompletiondate,'mm') || '月' planmonth
  FROM mfgorder mfg
  LEFT JOIN Ordertype ot ON ot.ordertypeid = mfg.ordertypeid
  LEFT JOIN Userattribute u ON u.parentid = mfg.mfgorderid AND u.userattributename = '发动机'
  LEFT JOIN Userattribute u2 ON u2.parentid = mfg.mfgorderid AND u2.userattributename = '订单类别'
  WHERE extract(year from mfg.factorycompletiondate) = extract(year from SYSDATE)
        AND NVL(u2.attributevalue,DECODE(ot.ordertypename,'PD01','批产','PD02','新机','PD03','新机','PD05','大修','PD06','检修')) = '检修'
) t
LEFT JOIN
(
  SELECT to_char(mfg.factorycompletiondate,'mm') || '月' planmonth,mfg.mfgordername,u.attributevalue,
         ml.finishdate,NVL(u2.attributevalue,DECODE(ot.ordertypename,'PD01','批产','PD02','新机','PD03','新机','PD05','大修','PD06','检修')) ordertypename
  FROM mfgorder mfg
  LEFT JOIN Orderstatus od ON od.orderstatusid = mfg.orderstatusid
  LEFT JOIN ( SELECT ml.mfgorderid,MAX(ml.operatetime) finishdate FROM mfgorderoperatelog ml GROUP BY ml.mfgorderid ) ml ON ml.mfgorderid = mfg.mfgorderid
  LEFT JOIN Ordertype ot ON ot.ordertypeid = mfg.ordertypeid
  LEFT JOIN Userattribute u ON u.parentid = mfg.mfgorderid AND u.userattributename = '发动机'
  LEFT JOIN Userattribute u2 ON u2.parentid = mfg.mfgorderid AND u2.userattributename = '订单类别'
  WHERE extract(year from mfg.factorycompletiondate) = extract(year from SYSDATE) AND od.orderstatusname='关闭'
        AND extract(year from ml.finishdate) = extract(year from SYSDATE)
        AND NVL(u2.attributevalue,DECODE(ot.ordertypename,'PD01','批产','PD02','新机','PD03','新机','PD05','大修','PD06','检修')) = '检修'
) m ON t.planmonth = m.planmonth AND t.mfgordername = m.mfgordername
ORDER BY t.planmonth,t.attributevalue
";

            uMESPagingDataDTO retR = dal.GetPagingDataIns(sql, Convert.ToInt32(para["page"]), Convert.ToInt32(para["rows"]));
            pageCount = Convert.ToInt32(retR.PageCount);
            rowCount = Convert.ToInt32(retR.RowCount);

            var json = JsonConvert.SerializeObject(retR.DBTable);
            context.Response.ContentType = "text/json";
            json = "{\"data\":" + json.ToString()
                    + ",\"count\":" + rowCount
                    + ",\"code\":" + "0"
                    + ",\"msg\":" + "0"
                    + "}";
            context.Response.Write(json.ToString());
            context.Response.End();
        }

        //年度数据
        if (context.Request.Form["type"] != null && context.Request.Form["type"] == "GetTotal")
        {
            Dictionary<string, string> para = new Dictionary<string, string>();
            if (context.Request.Form["ordertypename"] != null)
            {
                para["ordertypename"] = context.Request.Form["ordertypename"].ToString();
            }

            string sql = "";

            if (para["ordertypename"].ToString() == "GetPiChan")
            {
                //                sql = @"
                //SELECT '批产' ordertypename,nvl(yp.batchproduct,0) planQty,te.finishqty
                //FROM zsyearplanproduce yp
                //LEFT JOIN (
                //    SELECT '批产' ordertypename,COUNT(1) finishqty FROM
                //    (
                //        SELECT to_char(mfg.factorycompletiondate,'mm') || '月' planmonth,mfg.mfgordername,u.attributevalue,
                //               max(ep.finishdate) finishdate,DECODE(ep.usecompleted,0,'未完工',1,'已完工') isfinished,
                //               NVL(u2.attributevalue,DECODE(ot.ordertypename,'PD01','批产','PD02','新机','PD03','新机','PD05','大修','PD06','检修')) ordertypename
                //        FROM enginepartplan ep
                //        LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
                //        LEFT JOIN Userattribute u ON u.parentid=ep.mfgorderid AND u.userattributename='发动机'
                //        LEFT JOIN Ordertype ot ON ot.ordertypeid = mfg.ordertypeid
                //        LEFT JOIN Userattribute u2 ON u2.parentid=ep.mfgorderid AND u2.userattributename = '订单类别'
                //        LEFT JOIN workflowstep ws ON ws.workflowstepid = ep.workflowstepid
                //        WHERE extract(year from mfg.factorycompletiondate) = extract(year from SYSDATE)
                //              AND NVL(u2.attributevalue,DECODE(ot.ordertypename,'PD01','批产','PD02','新机','PD03','新机','PD05','大修','PD06','检修')) = '批产'
                //              AND ep.type = 1 AND ws.workflowstepname = '0120-检验试车' AND ws.sequence < 13
                //        GROUP BY mfg.factorycompletiondate,mfg.mfgordername,u.attributevalue,
                //                 ep.usecompleted,NVL(u2.attributevalue,DECODE(ot.ordertypename,'PD01','批产','PD02','新机','PD03','新机','PD05','大修','PD06','检修'))
                //    ) t WHERE t.isfinished = '已完工'
                //) te ON te.ordertypename = '批产'
                //WHERE yp.planyear = to_char(SYSDATE,'yyyy')
                //";
                sql = @"

SELECT '批产' ordertypename,nvl(yp.batchproduct,0) planQty,te.finishqty
FROM zsyearplanproduce yp
LEFT JOIN
(
  SELECT '批产' ordertypename,COUNT(1) finishqty FROM
  (
    SELECT t.planmonth,t.mfgordername,t.attributevalue,t.ordertypename,
           CASE WHEN COUNT(t.containerno) = SUM(t.usecompleted) THEN '已完工' ELSE '未完工' END isfinished,
           CASE WHEN COUNT(t.containerno) = SUM(t.usecompleted) THEN MAX(t.finishdate) ELSE NULL END finishdate
    FROM 
    (
        SELECT  to_char(mfg.factorycompletiondate,'mm') || '月' planmonth,mfg.mfgordername,u.attributevalue,ep.containerno,
                max(ep.finishdate) finishdate,ep.usecompleted,--DECODE(ep.usecompleted,0,'未完工',1,'已完工') isfinished,
                NVL(u2.attributevalue,DECODE(ot.ordertypename,'PD01','批产','PD02','新机','PD03','新机','PD05','大修','PD06','检修')) ordertypename
        FROM enginepartplan ep
        LEFT JOIN mfgorder mfg ON mfg.mfgorderid = ep.mfgorderid
        LEFT JOIN Userattribute u ON u.parentid=ep.mfgorderid AND u.userattributename='发动机'
        LEFT JOIN Ordertype ot ON ot.ordertypeid = mfg.ordertypeid
        LEFT JOIN Userattribute u2 ON u2.parentid=ep.mfgorderid AND u2.userattributename = '订单类别'
        LEFT JOIN workflowstep ws ON ws.workflowstepid = ep.workflowstepid
        WHERE extract(year from mfg.factorycompletiondate) = extract(year from SYSDATE) --计划完成日期在本年
              AND NVL(u2.attributevalue,DECODE(ot.ordertypename,'PD01','批产','PD02','新机','PD03','新机','PD05','大修','PD06','检修')) = '批产'
              AND ep.type = 1 AND ws.workflowstepname = '0120-检验试车' AND ws.sequence < 13
              AND ep.containerno IS NOT NULL
        GROUP BY mfg.factorycompletiondate,mfg.mfgordername,u.attributevalue,ep.containerno,
                 ep.usecompleted,NVL(u2.attributevalue,DECODE(ot.ordertypename,'PD01','批产','PD02','新机','PD03','新机','PD05','大修','PD06','检修'))
    ) t
    GROUP BY t.planmonth,t.mfgordername,t.attributevalue,t.ordertypename
    
  ) t2 WHERE t2.isfinished = '已完工'
  
) te ON te.ordertypename = '批产'
WHERE yp.planyear = to_char(SYSDATE,'yyyy')
";
            }
            else if (para["ordertypename"].ToString() == "GetXinJi")
            {
                sql = @"
SELECT '新机' ordertypename,COUNT(t.mfgordername) planQty,COUNT(m.mfgordername) finishqty
FROM
(
  SELECT NVL(u2.attributevalue,DECODE(ot.ordertypename,'PD02','新机','PD03','新机','PD06','检修')) ordertypename,u.attributevalue,
         mfg.mfgordername,to_char(mfg.factorycompletiondate,'mm') || '月' planmonth
  FROM mfgorder mfg
  LEFT JOIN Ordertype ot ON ot.ordertypeid = mfg.ordertypeid
  LEFT JOIN Userattribute u ON u.parentid = mfg.mfgorderid AND u.userattributename = '发动机'
  LEFT JOIN Userattribute u2 ON u2.parentid = mfg.mfgorderid AND u2.userattributename = '订单类别'
  WHERE extract(year from mfg.factorycompletiondate) = extract(year from SYSDATE)
        AND NVL(u2.attributevalue,DECODE(ot.ordertypename,'PD01','批产','PD02','新机','PD03','新机','PD05','大修','PD06','检修')) = '新机'
) t
LEFT JOIN
(
  SELECT to_char(mfg.factorycompletiondate,'mm') || '月' planmonth,mfg.mfgordername,u.attributevalue,
         ml.finishdate,NVL(u2.attributevalue,DECODE(ot.ordertypename,'PD01','批产','PD02','新机','PD03','新机','PD05','大修','PD06','检修')) ordertypename
  FROM mfgorder mfg
  LEFT JOIN Orderstatus od ON od.orderstatusid = mfg.orderstatusid
  LEFT JOIN ( SELECT ml.mfgorderid,MAX(ml.operatetime) finishdate FROM mfgorderoperatelog ml GROUP BY ml.mfgorderid ) ml ON ml.mfgorderid = mfg.mfgorderid
  LEFT JOIN Ordertype ot ON ot.ordertypeid = mfg.ordertypeid
  LEFT JOIN Userattribute u ON u.parentid = mfg.mfgorderid AND u.userattributename = '发动机'
  LEFT JOIN Userattribute u2 ON u2.parentid = mfg.mfgorderid AND u2.userattributename = '订单类别'
  WHERE extract(year from mfg.factorycompletiondate) = extract(year from SYSDATE) AND od.orderstatusname='关闭'
        AND extract(year from ml.finishdate) = extract(year from SYSDATE)
        AND NVL(u2.attributevalue,DECODE(ot.ordertypename,'PD01','批产','PD02','新机','PD03','新机','PD05','大修','PD06','检修')) = '新机'
) m ON t.mfgordername = m.mfgordername
";
            }
            else
            {
                sql = @"
SELECT '检修' ordertypename,COUNT(t.mfgordername) planQty,COUNT(m.mfgordername) finishqty
FROM
(
  SELECT NVL(u2.attributevalue,DECODE(ot.ordertypename,'PD02','新机','PD03','新机','PD06','检修')) ordertypename,u.attributevalue,
         mfg.mfgordername,to_char(mfg.factorycompletiondate,'mm') || '月' planmonth
  FROM mfgorder mfg
  LEFT JOIN Ordertype ot ON ot.ordertypeid = mfg.ordertypeid
  LEFT JOIN Userattribute u ON u.parentid = mfg.mfgorderid AND u.userattributename = '发动机'
  LEFT JOIN Userattribute u2 ON u2.parentid = mfg.mfgorderid AND u2.userattributename = '订单类别'
  WHERE extract(year from mfg.factorycompletiondate) = extract(year from SYSDATE)
        AND NVL(u2.attributevalue,DECODE(ot.ordertypename,'PD01','批产','PD02','新机','PD03','新机','PD05','大修','PD06','检修')) = '检修'
) t
LEFT JOIN
(
  SELECT to_char(mfg.factorycompletiondate,'mm') || '月' planmonth,mfg.mfgordername,u.attributevalue,
         ml.finishdate,NVL(u2.attributevalue,DECODE(ot.ordertypename,'PD01','批产','PD02','新机','PD03','新机','PD05','大修','PD06','检修')) ordertypename
  FROM mfgorder mfg
  LEFT JOIN Orderstatus od ON od.orderstatusid = mfg.orderstatusid
  LEFT JOIN ( SELECT ml.mfgorderid,MAX(ml.operatetime) finishdate FROM mfgorderoperatelog ml GROUP BY ml.mfgorderid ) ml ON ml.mfgorderid = mfg.mfgorderid
  LEFT JOIN Ordertype ot ON ot.ordertypeid = mfg.ordertypeid
  LEFT JOIN Userattribute u ON u.parentid = mfg.mfgorderid AND u.userattributename = '发动机'
  LEFT JOIN Userattribute u2 ON u2.parentid = mfg.mfgorderid AND u2.userattributename = '订单类别'
  WHERE extract(year from mfg.factorycompletiondate) = extract(year from SYSDATE) AND od.orderstatusname='关闭'
        AND extract(year from ml.finishdate) = extract(year from SYSDATE)
        AND NVL(u2.attributevalue,DECODE(ot.ordertypename,'PD01','批产','PD02','新机','PD03','新机','PD05','大修','PD06','检修')) = '检修'
) m ON t.mfgordername = m.mfgordername
";
            }

            DataTable dt = dal.GetDataTable(sql);
            var json = JsonConvert.SerializeObject(dt);
            context.Response.ContentType = "text/json";
            context.Response.Write(json.ToString());
            context.Response.End();
        }
    }
}